<!DOCTYPE html>
<html>

<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
	<meta name="theme-color" content="#33474d">
	<title>mysql登录 | 失落的乐章</title>
	<link rel="stylesheet" href="/css/style.css" />
	
      <link rel="alternate" href="/atom.xml" title="失落的乐章" type="application/atom+xml">
    
</head>

<body>

	<header class="header">
		<nav class="header__nav">
			
				<a href="/archives" class="header__link">Archive</a>
			
				<a href="/tags" class="header__link">Tags</a>
			
				<a href="/atom.xml" class="header__link">RSS</a>
			
		</nav>
		<h1 class="header__title"><a href="/">失落的乐章</a></h1>
		<h2 class="header__subtitle">技术面前，永远都是学生。</h2>
	</header>

	<main>
		<article>
	
		<h1>mysql登录</h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql服务启动时，不仅会监听 IP:Port，还会监听一个socket。我们安装的mysql是监听在/tmp/mysql.sock。如果php是在本地，那么php和mysql通信可以通过socket通信，如果是远程，就需要通过tcp/ip来通信了。在Linux命令行下，可以通过如下方法来连接mysql服务器。</p>
<h2 id="tcp-ip的方式"><a href="#tcp-ip的方式" class="headerlink" title="tcp/ip的方式"></a>tcp/ip的方式</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># /usr/local/mysql/bin/mysql -uroot -h 127.0.0.1</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这样就连接上了，如果默认mysql的root用户密码为空，就不用加-p选项，不过最好还是设置一个密码。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># /usr/local/mysql/bin/mysqladmin -uroot password 'yanyi'</span></div></pre></td></tr></table></figure>
<p>`&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只是第一次设置密码的时候可以这样，再次设置时，就需要先输入之前的root密码了。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># /usr/local/mysql/bin/mysqladmin -uroot -pyanyi password '123456'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;还有个问题，每次敲命令都是用的绝对路径，这样很繁琐，两个办法可以解决。第一个办法是设置alias，第二个办法是设置PATH</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># alias mysql=/usr/local/mysql/bin/mysql</span></div><div class="line">[root@lamp ~]<span class="comment"># alias mysqladmin=/usr/local/mysql/bin/mysqladmin</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果要永久生效，需要把两个alias放到 <code>.bashrc</code> 里面。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># vim .bashrc</span></div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E7%99%BB%E5%BD%95/01.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;另外就是设置PATH</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># vim /etc/profile.d/path.sh</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;加入</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">export</span> PATH=<span class="variable">$PATH</span>:/usr/<span class="built_in">local</span>/mysql/bin</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;保存后执行</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># source /etc/profile.d/path.sh</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;当给mysql设置密码后再去连接，就需要加上-p选项了</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -pyanyi -h127.0.0.1</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;其中-h指定ip，如果是远程机器，则-h后面跟远程服务器ip，默认port是3306，如果是其他端口，需要用-P来定义</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -pyanyi -h127.0.0.1 -P3306</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;另外有时候还需要mysql授权客户机，要不无法登录</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -pyanyi</span></div><div class="line"></div><div class="line">mysql&gt; grant all on *.* to <span class="string">'root'</span>@<span class="string">'192.168.0.99'</span> identified by <span class="string">'112233'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;all就是所有权限。<em>.</em>前边的*表示所有的库，后边的*表示所有的表，这里就是所有的库和所有的表。’root’@’192.168.99’ root表示授权给root，192.168.0.99是客户端ip，授权哪个客户端IP。<code>identifined by</code> 后边跟的密码</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; use mysql</div><div class="line">mysql&gt; selct * from user <span class="built_in">where</span> host=<span class="string">'192.168.0.99'</span>\G;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E7%99%BB%E5%BD%95/02.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;再次连接</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -h192.168.0.99 -p112233</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这里-p选项跟的密码是授权设置的密码，不是root的密码。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;登录以后查看一下当前登录用户</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select user();</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E7%99%BB%E5%BD%95/03.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;用户是 root@192.168.0.99</p>
<h2 id="socket方式"><a href="#socket方式" class="headerlink" title="socket方式"></a>socket方式</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这种只适合连接本机的mysql：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -S /tmp/mysql.sock -pyanyi</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这里的-S可以省略掉。</p>

	

	
		<span class="different-posts"><a href="/2017/10/12/MySQL/11.  mysql登录/" onclick="window.history.go(-1); return false;">⬅️ Go back </a></span>

	

</article>

	</main>

	<footer class="footer">
	<div class="footer-content">
		
	      <div class="footer__element">
	<p>Hi there, <br />welcome to my Blog glad you found it. Have a look around, will you?</p>
</div>

	    
	      <div class="footer__element">
	<h5>Check out</h5>
	<ul class="footer-links">
		<li class="footer-links__link"><a href="/archives">Archive</a></li>
		
		  <li class="footer-links__link"><a href="/atom.xml">RSS</a></li>
	    
		<li class="footer-links__link"><a href="/about">about page</a></li>
		<li class="footer-links__link"><a href="/tags">Tags</a></li>
		<li class="footer-links__link"><a href="/categories">Categories</a></li>
	</ul>
</div>

	    

		<div class="footer-credit">
			<span>© 2017 失落的乐章 | Powered by <a href="https://hexo.io/">Hexo</a> | Theme <a href="https://github.com/HoverBaum/meilidu-hexo">MeiliDu</a></span>
		</div>

	</div>


</footer>



</body>

</html>
